<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>zabbix进行数据库备份以及表分区 | 失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		<article>
	
		<h1>zabbix进行数据库备份以及表分区</h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/Zabbix/">Zabbix</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;由于测试环境上面使用的zabbix服务器配置比较低，经常会遇到性能瓶颈（主要是数据库和磁盘I/O等），于是倒逼我使用了一些方式来缓解这些问题。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;主要是以前使用的那个备份数据库的脚本是对zabbix数据库进行全备的，使用的又是mysql自带的工具mysqldump，当数据量大了之后进行全备所花的时间比较长，这样将会造成数据库的锁读。。。从而使zabbix服务以为mysql死掉了，产生一大堆的报警。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;后来发现原来造成数据库数据量大量增加的是zabbix数据库中的一些存储数据的大表导致的。于是备份数据库的时候可以选择跳过这些表进行备份，这样，将大大减少数据库备份所花的时间（PS：之前备份数据库所花时间在十分钟左右，现在跳过大表备份，所花时间在1S左右就能备份完，大大缩短了备份数据库时间）。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;下面就贴出某位大神写的专门为zabbix数据库做备份以及恢复的脚本：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div><div class="line">55</div><div class="line">56</div><div class="line">57</div><div class="line">58</div><div class="line">59</div><div class="line">60</div><div class="line">61</div><div class="line">62</div><div class="line">63</div><div class="line">64</div><div class="line">65</div><div class="line">66</div><div class="line">67</div><div class="line">68</div><div class="line">69</div><div class="line">70</div><div class="line">71</div><div class="line">72</div><div class="line">73</div><div class="line">74</div><div class="line">75</div><div class="line">76</div><div class="line">77</div><div class="line">78</div><div class="line">79</div><div class="line">80</div><div class="line">81</div></pre></td><td class="code"><pre><div class="line"><span class="meta">#!/bin/bash</span></div><div class="line"><span class="comment">#author: itnihao</span></div><div class="line">red=<span class="string">'\e[0;31m'</span> <span class="comment"># 红色 </span></div><div class="line">RED=<span class="string">'\e[1;31m'</span> </div><div class="line">green=<span class="string">'\e[0;32m'</span> <span class="comment"># 绿色 </span></div><div class="line">GREEN=<span class="string">'\e[1;32m'</span> </div><div class="line">blue=<span class="string">'\e[0;34m'</span> <span class="comment"># 蓝色 </span></div><div class="line">BLUE=<span class="string">'\e[1;34m'</span> </div><div class="line">purple=<span class="string">'\e[0;35m'</span> <span class="comment"># 紫色 </span></div><div class="line">PURPLE=<span class="string">'\e[1;35m'</span> </div><div class="line">NC=<span class="string">'\e[0m'</span> <span class="comment"># 没有颜色 </span></div><div class="line"><span class="built_in">source</span> /etc/bashrc</div><div class="line"><span class="built_in">source</span> /etc/profile</div><div class="line">MySQL_USER=zabbix</div><div class="line">MySQL_PASSWORD=zabbix</div><div class="line">MySQL_HOST=localhost</div><div class="line">MySQL_PORT=3306</div><div class="line">MySQL_DUMP_PATH=/opt/backup</div><div class="line">MYSQL_BIN_PATH=/opt/software/mysql/bin/mysql</div><div class="line">MYSQL_DUMP_BIN_PATH=/opt/software/mysql/bin/mysqldump</div><div class="line">MySQL_DATABASE_NAME=zabbix</div><div class="line">DATE=$(date <span class="string">'+%Y%m%d'</span>)</div><div class="line"><span class="function"><span class="title">MySQLDUMP</span></span> () &#123;</div><div class="line"> [ -d <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span> ] || mkdir <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span></div><div class="line"> <span class="built_in">cd</span> <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span></div><div class="line"> [ -d logs ] || mkdir logs</div><div class="line"> [ -d <span class="variable">$&#123;DATE&#125;</span> ] || mkdir <span class="variable">$&#123;DATE&#125;</span></div><div class="line"> <span class="built_in">cd</span> <span class="variable">$&#123;DATE&#125;</span></div><div class="line">  </div><div class="line"> <span class="comment">#TABLE_NAME_ALL=$($&#123;MYSQL_BIN_PATH&#125; -u$&#123;MySQL_USER&#125; -p$&#123;MySQL_PASSWORD&#125; -h$&#123;MySQL_HOST&#125; $&#123;MySQL_DATABASE_NAME&#125; -e "show tables"|egrep -v "(Tables_in_zabbix)")</span></div><div class="line"> TABLE_NAME_ALL=$(<span class="variable">$&#123;MYSQL_BIN_PATH&#125;</span> -u<span class="variable">$&#123;MySQL_USER&#125;</span> -p<span class="variable">$&#123;MySQL_PASSWORD&#125;</span> -h<span class="variable">$&#123;MySQL_HOST&#125;</span> <span class="variable">$&#123;MySQL_DATABASE_NAME&#125;</span> -e <span class="string">"show tables"</span>|egrep -v <span class="string">"(Tables_in_zabbix|history*|trends*|acknowledges|alerts|auditlog|events|service_alarms)"</span>)</div><div class="line"> <span class="keyword">for</span> TABLE_NAME <span class="keyword">in</span> <span class="variable">$&#123;TABLE_NAME_ALL&#125;</span></div><div class="line"> <span class="keyword">do</span></div><div class="line"> <span class="variable">$&#123;MYSQL_DUMP_BIN_PATH&#125;</span> --opt -u<span class="variable">$&#123;MySQL_USER&#125;</span> -p<span class="variable">$&#123;MySQL_PASSWORD&#125;</span> -P<span class="variable">$&#123;MySQL_PORT&#125;</span> -h<span class="variable">$&#123;MySQL_HOST&#125;</span> <span class="variable">$&#123;MySQL_DATABASE_NAME&#125;</span> <span class="variable">$&#123;TABLE_NAME&#125;</span> &gt;<span class="variable">$&#123;TABLE_NAME&#125;</span>.sql</div><div class="line"> sleep 0.01</div><div class="line"> <span class="keyword">done</span></div><div class="line"> [ <span class="string">"$?"</span> == 0 ] &amp;&amp; <span class="built_in">echo</span> <span class="string">"<span class="variable">$&#123;DATE&#125;</span>: Backup zabbix succeed"</span> &gt;&gt; <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span>/logs/ZabbixMysqlDump.log</div><div class="line"> [ <span class="string">"$?"</span> != 0 ] &amp;&amp; <span class="built_in">echo</span> <span class="string">"<span class="variable">$&#123;DATE&#125;</span>: Backup zabbix not succeed"</span> &gt;&gt; <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span>/logs/ZabbixMysqlDump.log</div><div class="line">  </div><div class="line"> <span class="built_in">cd</span> <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span>/</div><div class="line"> rm -rf $(date +%Y%m%d --date=<span class="string">'5 days ago'</span>)</div><div class="line"> <span class="built_in">exit</span> 0</div><div class="line">&#125;</div><div class="line"><span class="function"><span class="title">MySQLImport</span></span> () &#123;</div><div class="line"> <span class="built_in">cd</span> <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span></div><div class="line"> DATE=$(ls <span class="variable">$&#123;MySQL_DUMP_PATH&#125;</span> |egrep <span class="string">"\b^[0-9]+$\b"</span>)</div><div class="line"> <span class="built_in">echo</span> -e <span class="string">"<span class="variable">$&#123;green&#125;</span><span class="variable">$&#123;DATE&#125;</span>"</span></div><div class="line"> <span class="built_in">echo</span> -e <span class="string">"<span class="variable">$&#123;blue&#125;</span>what DATE do you want to import,please input date:<span class="variable">$&#123;NC&#125;</span>"</span></div><div class="line"> <span class="built_in">read</span> SELECT_DATE</div><div class="line"> <span class="keyword">if</span> [ -d <span class="string">"<span class="variable">$&#123;SELECT_DATE&#125;</span>"</span> ];<span class="keyword">then</span></div><div class="line"> <span class="built_in">echo</span> -e <span class="string">"you select is <span class="variable">$&#123;green&#125;</span><span class="variable">$&#123;SELECT_DATE&#125;</span><span class="variable">$&#123;NC&#125;</span>, do you want to contine,if,input <span class="variable">$&#123;red&#125;</span>(yes|y|Y)<span class="variable">$&#123;NC&#125;</span>,else then exit"</span></div><div class="line"> <span class="built_in">read</span> Input</div><div class="line"> [[ <span class="string">'yes|y|Y'</span> =~ <span class="string">"<span class="variable">$&#123;Input&#125;</span>"</span> ]]</div><div class="line"> status=<span class="string">"$?"</span></div><div class="line"> <span class="keyword">if</span> [ <span class="string">"<span class="variable">$&#123;status&#125;</span>"</span> == <span class="string">"0"</span> ];<span class="keyword">then</span></div><div class="line"> <span class="built_in">echo</span> <span class="string">"now import SQL....... Please wait......."</span></div><div class="line"> <span class="keyword">else</span></div><div class="line"> <span class="built_in">exit</span> 1</div><div class="line"> <span class="keyword">fi</span></div><div class="line"> <span class="built_in">cd</span> <span class="variable">$&#123;SELECT_DATE&#125;</span></div><div class="line"> <span class="keyword">for</span> PER_TABEL_SQL <span class="keyword">in</span> $(ls *.sql)</div><div class="line"> <span class="keyword">do</span></div><div class="line"> <span class="variable">$&#123;MYSQL_BIN_PATH&#125;</span> -u<span class="variable">$&#123;MySQL_USER&#125;</span> -p<span class="variable">$&#123;MySQL_PASSWORD&#125;</span> -h<span class="variable">$&#123;MySQL_HOST&#125;</span> <span class="variable">$&#123;MySQL_DATABASE_NAME&#125;</span> &lt; <span class="variable">$&#123;PER_TABEL_SQL&#125;</span></div><div class="line"> <span class="built_in">echo</span> -e <span class="string">"import <span class="variable">$&#123;PER_TABEL_SQL&#125;</span> <span class="variable">$&#123;PURPLE&#125;</span>........................<span class="variable">$&#123;NC&#125;</span>"</span></div><div class="line"> <span class="keyword">done</span> </div><div class="line"> <span class="built_in">echo</span> <span class="string">"Finish import SQL,Please check Zabbix database"</span></div><div class="line"> <span class="keyword">else</span> </div><div class="line"> <span class="built_in">echo</span> <span class="string">"Don't exist <span class="variable">$&#123;SELECT_DATE&#125;</span> DIR"</span> </div><div class="line"> <span class="keyword">fi</span></div><div class="line">&#125;</div><div class="line"><span class="keyword">case</span> <span class="string">"<span class="variable">$1</span>"</span> <span class="keyword">in</span></div><div class="line">MySQLDUMP|mysqldump)</div><div class="line"> MySQLDUMP</div><div class="line"> ;;</div><div class="line">MySQLImport|mysqlimport)</div><div class="line"> MySQLImport</div><div class="line"> ;;</div><div class="line">*)</div><div class="line"> <span class="built_in">echo</span> <span class="string">"Usage: <span class="variable">$0</span> &#123;(MySQLDUMP|mysqldump) (MySQLImport|mysqlimport)&#125;"</span></div><div class="line"> ;;</div><div class="line"><span class="keyword">esac</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;该脚本源出处在这<a href="https://github.com/itnihao/zabbix-book/blob/master/03-chapter/Zabbix_MySQLdump_per_table_v2.sh" target="_blank" rel="external">https://github.com/itnihao/zabbix-book/blob/master/03-chapter/Zabbix_MySQLdump_per_table_v2.sh</a></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是在大神的脚本上做了修改之后形成的适合我自己备份的脚本，各位也可以自行修改成适合自己的备份脚本。这个脚本实现的效果上面已经说了，之前做全备的时候差不多有4G左右的数据量，现在只备份配置文件数据量只有不到10M，果断大大节省时间以及空间呀。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不过这样的话将无法保证数据的备份，目前考虑使用xtradbbackup对数据进行增量备份，目前还未实现，留待过两天做吧。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;好了，关于数据库备份的事情搞了，然后还需要对大数据量的表进行表分区，参考了zabbix官网的一篇文章<a href="https://www.zabbix.org/wiki/Docs/howto/mysql_partition" target="_blank" rel="external">https://www.zabbix.org/wiki/Docs/howto/mysql_partition</a> 各位有兴趣的话可以去看看，我这里将其总结在了一起，更加方便一点。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;表分区可以对大数据量的表进行物理上的拆分成多个文件，但是逻辑上来看，还是一张表，对应用程序是透明的。另外，将这一张大表拆分成很多小表的话将使得数据查询速度能够更快。还可以随时删除旧的数据分区，删除过期数据。这种方式适用于大数据量的表，但是查询量比较少的应用场景。如果是大数据量的表，又有大量查询的话建议还是进行分库分表操作。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;首先，登录数据库(PS:这个就不演示了)</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后登陆到zabbix库中修改两张表的结构：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">use zabbix;</div><div class="line">Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);</div><div class="line">Alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改完之后再按照官网上的过程创建四个存储过程：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div></pre></td><td class="code"><pre><div class="line">DELIMITER $$</div><div class="line">CREATE PROCEDURE `partition_create`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)</div><div class="line">BEGIN</div><div class="line"> /*</div><div class="line"> SCHEMANAME = The DB schema <span class="keyword">in</span> <span class="built_in">which</span> to make changes</div><div class="line"> TABLENAME = The table with partitions to potentially delete</div><div class="line"> PARTITIONNAME = The name of the partition to create</div><div class="line"> */</div><div class="line"> /*</div><div class="line"> Verify that the partition does not already exist</div><div class="line"> */</div><div class="line">  </div><div class="line"> DECLARE RETROWS INT;</div><div class="line"> SELECT COUNT(1) INTO RETROWS</div><div class="line"> FROM information_schema.partitions</div><div class="line"> WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_description &gt;= CLOCK;</div><div class="line">  </div><div class="line"> IF RETROWS = 0 THEN</div><div class="line"> /*</div><div class="line"> 1. Print a message indicating that a partition was created.</div><div class="line"> 2. Create the SQL to create the partition.</div><div class="line"> 3. Execute the SQL from <span class="comment">#2.</span></div><div class="line"> */</div><div class="line"> SELECT CONCAT( <span class="string">"partition_create("</span>, SCHEMANAME, <span class="string">","</span>, TABLENAME, <span class="string">","</span>, PARTITIONNAME, <span class="string">","</span>, CLOCK, <span class="string">")"</span> ) AS msg;</div><div class="line"> SET @SQL = CONCAT( <span class="string">'ALTER TABLE '</span>, SCHEMANAME, <span class="string">'.'</span>, TABLENAME, <span class="string">' ADD PARTITION (PARTITION '</span>, PARTITIONNAME, <span class="string">' VALUES LESS THAN ('</span>, CLOCK, <span class="string">'));'</span> );</div><div class="line"> PREPARE STMT FROM @SQL;</div><div class="line"> EXECUTE STMT;</div><div class="line"> DEALLOCATE PREPARE STMT;</div><div class="line"> END IF;</div><div class="line">END</div><div class="line">$<span class="variable">$DELIMITER</span> ;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div><div class="line">55</div><div class="line">56</div><div class="line">57</div><div class="line">58</div><div class="line">59</div><div class="line">60</div><div class="line">61</div><div class="line">62</div></pre></td><td class="code"><pre><div class="line">DELIMITER $$</div><div class="line">CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)</div><div class="line">BEGIN</div><div class="line"> /*</div><div class="line"> SCHEMANAME = The DB schema <span class="keyword">in</span> <span class="built_in">which</span> to make changes</div><div class="line"> TABLENAME = The table with partitions to potentially delete</div><div class="line"> DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)</div><div class="line"> */</div><div class="line"> DECLARE <span class="keyword">done</span> INT DEFAULT FALSE;</div><div class="line"> DECLARE drop_part_name VARCHAR(16);</div><div class="line">  </div><div class="line"> /*</div><div class="line"> Get a list of all the partitions that are older than the date</div><div class="line"> <span class="keyword">in</span> DELETE_BELOW_PARTITION_DATE. All partitions are prefixed with</div><div class="line"> a <span class="string">"p"</span>, so use SUBSTRING TO get rid of that character.</div><div class="line"> */</div><div class="line"> DECLARE myCursor CURSOR FOR</div><div class="line"> SELECT partition_name</div><div class="line"> FROM information_schema.partitions</div><div class="line"> WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) &lt; DELETE_BELOW_PARTITION_DATE;</div><div class="line"> DECLARE CONTINUE HANDLER FOR NOT FOUND SET <span class="keyword">done</span> = TRUE;</div><div class="line">  </div><div class="line"> /*</div><div class="line"> Create the basics <span class="keyword">for</span> when we need to drop the partition. Also, create</div><div class="line"> @drop_partitions to hold a comma-delimited list of all partitions that</div><div class="line"> should be deleted.</div><div class="line"> */</div><div class="line"> SET @alter_header = CONCAT(<span class="string">"ALTER TABLE "</span>, SCHEMANAME, <span class="string">"."</span>, TABLENAME, <span class="string">" DROP PARTITION "</span>);</div><div class="line"> SET @drop_partitions = <span class="string">""</span>;</div><div class="line">  </div><div class="line"> /*</div><div class="line"> Start looping through all the partitions that are too old.</div><div class="line"> */</div><div class="line"> OPEN myCursor;</div><div class="line"> read_loop: LOOP</div><div class="line"> FETCH myCursor INTO drop_part_name;</div><div class="line"> IF <span class="keyword">done</span> THEN</div><div class="line"> LEAVE read_loop;</div><div class="line"> END IF;</div><div class="line"> SET @drop_partitions = IF(@drop_partitions = <span class="string">""</span>, drop_part_name, CONCAT(@drop_partitions, <span class="string">","</span>, drop_part_name));</div><div class="line"> END LOOP;</div><div class="line"> IF @drop_partitions != <span class="string">""</span> THEN</div><div class="line"> /*</div><div class="line"> 1. Build the SQL to drop all the necessary partitions.</div><div class="line"> 2. Run the SQL to drop the partitions.</div><div class="line"> 3. Print out the table partitions that were deleted.</div><div class="line"> */</div><div class="line"> SET @full_sql = CONCAT(@alter_header, @drop_partitions, <span class="string">";"</span>);</div><div class="line"> PREPARE STMT FROM @full_sql;</div><div class="line"> EXECUTE STMT;</div><div class="line"> DEALLOCATE PREPARE STMT;</div><div class="line">  </div><div class="line"> SELECT CONCAT(SCHEMANAME, <span class="string">"."</span>, TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;</div><div class="line"> ELSE</div><div class="line"> /*</div><div class="line"> No partitions are being deleted, so <span class="built_in">print</span> out <span class="string">"N/A"</span> (Not applicable) to indicate</div><div class="line"> that no changes were made.</div><div class="line"> */</div><div class="line"> SELECT CONCAT(SCHEMANAME, <span class="string">"."</span>, TABLENAME) AS `table`, <span class="string">"N/A"</span> AS `partitions_deleted`;</div><div class="line"> END IF;</div><div class="line">END$$</div><div class="line">DELIMITER ;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div></pre></td><td class="code"><pre><div class="line">DELIMITER $$</div><div class="line">CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)</div><div class="line">BEGIN</div><div class="line"> DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);</div><div class="line"> DECLARE PARTITION_NAME VARCHAR(16);</div><div class="line"> DECLARE LESS_THAN_TIMESTAMP INT;</div><div class="line"> DECLARE CUR_TIME INT;</div><div class="line">  </div><div class="line"> CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);</div><div class="line"> SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), <span class="string">'%Y-%m-%d 00:00:00'</span>));</div><div class="line">  </div><div class="line"> SET @__interval = 1;</div><div class="line"> create_loop: LOOP</div><div class="line"> IF @__interval &gt; CREATE_NEXT_INTERVALS THEN</div><div class="line"> LEAVE create_loop;</div><div class="line"> END IF;</div><div class="line">  </div><div class="line"> SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);</div><div class="line"> SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, <span class="string">'p%Y%m%d%H00'</span>);</div><div class="line"> CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);</div><div class="line"> SET @__interval=@__interval+1;</div><div class="line"> END LOOP;</div><div class="line">  </div><div class="line"> SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), <span class="string">'%Y%m%d0000'</span>);</div><div class="line"> CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);</div><div class="line">  </div><div class="line">END$$</div><div class="line">DELIMITER ;</div></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div></pre></td><td class="code"><pre><div class="line">DELIMITER $$</div><div class="line">CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))</div><div class="line">BEGIN</div><div class="line"> DECLARE PARTITION_NAME VARCHAR(16);</div><div class="line"> DECLARE RETROWS INT(11);</div><div class="line"> DECLARE FUTURE_TIMESTAMP TIMESTAMP;</div><div class="line">  </div><div class="line"> /*</div><div class="line"> * Check <span class="keyword">if</span> any partitions exist <span class="keyword">for</span> the given SCHEMANAME.TABLENAME.</div><div class="line"> */</div><div class="line"> SELECT COUNT(1) INTO RETROWS</div><div class="line"> FROM information_schema.partitions</div><div class="line"> WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL;</div><div class="line">  </div><div class="line"> /*</div><div class="line"> * If partitions <span class="keyword">do</span> not exist, go ahead and partition the table</div><div class="line"> */</div><div class="line"> IF RETROWS = 1 THEN</div><div class="line"> /*</div><div class="line"> * Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below <span class="built_in">which</span> we will store values.</div><div class="line"> * We begin partitioning based on the beginning of a day. This is because we don<span class="string">'t want to generate a random partition</span></div><div class="line"><span class="string"> * that won'</span>t necessarily fall <span class="keyword">in</span> line with the desired partition naming (ie: <span class="keyword">if</span> the hour interval is 24 hours, we could</div><div class="line"> * end up creating a partition now named <span class="string">"p201403270600"</span> when all other partitions will be like <span class="string">"p201403280000"</span>).</div><div class="line"> */</div><div class="line"> SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), <span class="string">" "</span>, <span class="string">'00:00:00'</span>));</div><div class="line"> SET PARTITION_NAME = DATE_FORMAT(CURDATE(), <span class="string">'p%Y%m%d%H00'</span>);</div><div class="line">  </div><div class="line"> -- Create the partitioning query</div><div class="line"> SET @__PARTITION_SQL = CONCAT(<span class="string">"ALTER TABLE "</span>, SCHEMANAME, <span class="string">"."</span>, TABLENAME, <span class="string">" PARTITION BY RANGE(`clock`)"</span>);</div><div class="line"> SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, <span class="string">"(PARTITION "</span>, PARTITION_NAME, <span class="string">" VALUES LESS THAN ("</span>, UNIX_TIMESTAMP(FUTURE_TIMESTAMP), <span class="string">"));"</span>);</div><div class="line">  </div><div class="line"> -- Run the partitioning query</div><div class="line"> PREPARE STMT FROM @__PARTITION_SQL;</div><div class="line"> EXECUTE STMT;</div><div class="line"> DEALLOCATE PREPARE STMT;</div><div class="line"> END IF;</div><div class="line">END$$</div><div class="line">DELIMITER ;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;上面四个存储过程执行后将可以使用</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">CALL partition_maintenance(<span class="string">'&lt;zabbix_db_name&gt;'</span>, <span class="string">'&lt;table_name&gt;'</span>, &lt;days_to_keep_data&gt;, &lt;hourly_interval&gt;, &lt;num_future_intervals_to_create&gt;)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;命令对想要分区的表进行表分区了。其中的参数我这里解释一下。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是举例：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">CALL partition_maintenance(zabbix, <span class="string">'history_uint'</span>, 31, 24, 14);</div></pre></td></tr></table></figure>
<ul>
<li>zabbix_db_name：库名</li>
<li>table_name：表名</li>
<li>days_to_keep_data：保存多少天的数据</li>
<li>hourly_interval：每隔多久生成一个分区</li>
<li>num_future_intervals_to_create：本次一共生成多少个分区</li>
</ul>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这个例子就是history_uint表最多保存31天的数据，每隔24小时生成一个分区，这次一共生成14个分区<br>这里可以将上面四个存储过程保存为一个文件，导入到数据库中，文件我稍后将会放在附件中，这里使用的命令是：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -uzabbix -pzabbix zabbix&lt;partition_call.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后可以将CALL统一调用也做成一个文件，统一调用的内容如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">DELIMITER $$</div><div class="line">CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))</div><div class="line">BEGIN</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'history'</span>, 31, 24, 14);</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'history_log'</span>, 31, 24, 14);</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'history_str'</span>, 31, 24, 14);</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'history_text'</span>, 31, 24, 14);</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'history_uint'</span>, 31, 24, 14);</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'trends'</span>, 180, 24, 14);</div><div class="line"> CALL partition_maintenance(SCHEMA_NAME, <span class="string">'trends_uint'</span>, 180, 24, 14);</div><div class="line">END$$</div><div class="line">DELIMITER ;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也将该文件导入到数据库中，使用命令：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -uzabbix -pzabbix zabbix&lt;partition_all.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;好了，到了这里之后就可以使用如下命令执行表分区了：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div></pre></td><td class="code"><pre><div class="line">mysql -uzabbix -pzabbix zabbix -e <span class="string">"CALL partition_maintenance_all('zabbix');"</span></div><div class="line">+----------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+----------------+--------------------+</div><div class="line">| zabbix.history | N/A |</div><div class="line">+----------------+--------------------+</div><div class="line">+--------------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+--------------------+--------------------+</div><div class="line">| zabbix.history_log | N/A |</div><div class="line">+--------------------+--------------------+</div><div class="line">+--------------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+--------------------+--------------------+</div><div class="line">| zabbix.history_str | N/A |</div><div class="line">+--------------------+--------------------+</div><div class="line">+---------------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+---------------------+--------------------+</div><div class="line">| zabbix.history_text | N/A |</div><div class="line">+---------------------+--------------------+</div><div class="line">+---------------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+---------------------+--------------------+</div><div class="line">| zabbix.history_uint | N/A |</div><div class="line">+---------------------+--------------------+</div><div class="line">+---------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+---------------+--------------------+</div><div class="line">| zabbix.trends | N/A |</div><div class="line">+---------------+--------------------+</div><div class="line">+--------------------+--------------------+</div><div class="line">| table | partitions_deleted |</div><div class="line">+--------------------+--------------------+</div><div class="line">| zabbix.trends_uint | N/A |</div><div class="line">+--------------------+--------------------+</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;看到如下结果证明所有7张表都进行了表分区，也可以在Mysql的数data目录下看到新生成的表分区文件。（PS:注意，最好是清空history_uint表的数据之后再执行上面这条命令，否则因为这张表数据量太大，转换时间将会好长，清空表中数据的命令为： truncate table history_uint;）</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;好了，这样可以进行表分区了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;将上面这条命令写入到计划任务中如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">crontab -l|tail -1</div><div class="line">01 01 * * * /opt/software/mysql/bin/mysql -uzabbix -pzabbix zabbix -e <span class="string">"CALL partition_maintenance_all('zabbix');"</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;每天晚上的1点01执行一次。还有之前写的备份数据库的脚本也需要执行计划任务每天的凌晨0点01执行备份：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">crontab -l|tail -2|head -1</div><div class="line">01 00 * * * /usr/<span class="built_in">local</span>/scripts/Zabbix_MySQLdump_per_table_v2.sh mysqldump</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这样就大功告成了，之后再体验一下zabbix的web页面看是不是感觉比以前快了？</p>

	

	
		<span class="different-posts"><a href="/2017/10/12/Zabbix/19. zabbix进行数据库备份以及表分区/" onclick="window.history.go(-1); return false;">⬅️ Go back </a></span>

	

</article>

	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
